package com.dog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.util.db.DBUtils;
import com.dog.dao.CrawlDao;
import com.dog.domain.Crawl;


public  class CrawlDaoImpl implements CrawlDao{


	@Override
	public void add(Crawl c) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "insert into crawl(id,url,abs,description)values(?,?,?,?)";
		try{
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, c.getId());
			ps.setString(2, c.getUrl());
			ps.setString(3, c.getAbs());
			ps.setString(4, c.getDescription());
			ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
			throw new SQLException("*");
		}finally{
			DBUtils.close(null, ps, conn);
		}
	}


	@Override
	public void update(Crawl c) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update crawl set url=?,abs=?,description=? where id=?";
		try{
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, c.getUrl());
			ps.setString(2, c.getAbs());
			ps.setString(3, c.getDescription());
			ps.setInt(4, c.getId());
			ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
			throw new SQLException("*");
		}finally{
			DBUtils.close(null, ps, conn);
		}		
	}


	@Override
	public void delete(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "delete from crawl where id=?";
		try{
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1,id);
			ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
			throw new SQLException("*");
		}finally{
			DBUtils.close(null, ps, conn);
		}		
	}

	
	@Override
	public Crawl findById(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Crawl p = null;
		String sql = "select url,abs,description from crawl where id=?";
		try{
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				p = new Crawl();
				p.setId(id);
				p.setUrl(rs.getString(1));
				p.setAbs(rs.getString(2));
				p.setDescription(rs.getString(3));
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw new SQLException("*");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		return p;
	}

	
	@Override
	public List<Crawl> findAll() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Crawl p = null;
		List<Crawl> crawls = new ArrayList<Crawl>();
		String sql = "select id,url,abs,description from crawl";//这个也可以用*代替
		try{
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				p = new Crawl();
				p.setId(rs.getInt(1));
				p.setUrl(rs.getString(2));
				p.setAbs(rs.getString(3));
				p.setDescription(rs.getString(4));
				crawls.add(p);
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw new SQLException("*");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		return crawls;
	}

}